<?php

namespace app\index\controller;

use app\index\validate\Valid;
use think\Controller;
use think\Session;
use think\Request;
use think\View;
use think\Db;
use think\Model;
use excel\ExcelDownload;
use excel\ExcelReader;
use PHPExcel;
use PHPExcel_IOFactory;
use PHPExcel_Writer_Excel5;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Fill;
use PHPExcel_Style_Border;
//物流商模块
class Wls extends Common
{
    /**
     * 物流商模块
     */
    public function index()
    {
	     $sn = $this->request->param('sn');
	     $title = $this->request->param('title');
	     $this->assign([
	         'title'=>$title,
	         'sn'=>$sn,
         ]);
	     $where = [];
	     if($sn){
	         $where['sn'] = ['like','%'.trim($sn).'%'];
         }
	     if($title){
	         $where['title'] = ['like','%'.trim($title).'%'];
         }
        $cnt = db('cg_wlgs')->where($where)->count();
        $data = db('cg_wlgs')->where($where)->paginate(10, $cnt);
        $page = $data->render();
        $data = $data->items();
        $maxpage=ceil($cnt/10);
        $this->assign([
            'data' => $data,
            'page' => $page,
            'cnt' =>$cnt,
            'maxpage'=>$maxpage
        ]);

        return $this->fetch();
    }

    public function produceSN(){
        //生成编号
        $max = db('cg_wlgs')->where('sn','like',date('Ymd').'%')->max('cast(sn as SIGNED INTEGER)'); //编号递增开始位置基础

        if($max){
            $max = (double)$max;
            $insert_sn = $max+1;
        }else{
            $insert_sn = date('Ymd').'0001';
        }
        return $insert_sn;
    }

    /**
     * 新增物流商
     */
    public function add()
    {
        if($_POST){
            $data = [];
            $data['sn'] = trim($this->request->param('sn'));
            $data['title'] = trim($this->request->param('title'));
            $data['wlsqc'] = trim($this->request->param('wlsqc'));
            $data['wlsdz'] = trim($this->request->param('wlsdz'));
            $data['lxr'] = trim($this->request->param('lxr'));
            $data['dh'] = trim($this->request->param('dh'));
            $data['gscz'] = trim($this->request->param('gscz'));
            $data['yjdz'] = trim($this->request->param('yjdz'));
            $data['sjr'] = trim($this->request->param('sjr'));
            $data['sjrdh'] = trim($this->request->param('sjrdh'));
            $data['bz'] = trim($this->request->param('bz'));
            $valid = new Valid([
                'sn'=>'number|unique:cg_wlgs',
                'title'=>'require',
                'wlsqc'=>'require',
                'wlsdz'=>'require',
                'lxr'=>'require',
                'dh'=>'require',
                'yjdz'=>'require',
                'sjr'=>'require',
                'sjrdh'=>'require',
            ]);

            if(!$valid->check($data)){
                echo json_encode(['success'=>false,'msg'=>$valid->getError()]);die;
            }

            if(!$data['sn']){
                $data['sn'] = $this->produceSN();
            }
            $attach = trim($this->request->post('attach'));
            if($attach)
                $data['attach'] = serialize(explode(',',$attach));
            $data['cuser'] = session('employee_id');
            $data['ctime'] = date('Y-m-d H:i:s');
            $bool = db('cg_wlgs')->insert($data);
            if(false!==$bool){
                echo json_encode(['success'=>true,'msg'=>'添加成功']);
            }else{
                echo json_encode(['success'=>false,'msg'=>'添加失败']);
            }
            die;
        }
        return $this->fetch('form');
    }

    /**
     * 修改物流商
     */
    public function update()
    {
        $id = $this->request->param('id');
        if($_POST){
            $data = [];
            $data['title'] = trim($this->request->param('title'));
            $data['wlsqc'] = trim($this->request->param('wlsqc'));
            $data['wlsdz'] = trim($this->request->param('wlsdz'));
            $data['lxr'] = trim($this->request->param('lxr'));
            $data['dh'] = trim($this->request->param('dh'));
            $data['gscz'] = trim($this->request->param('gscz'));
            $data['yjdz'] = trim($this->request->param('yjdz'));
            $data['sjr'] = trim($this->request->param('sjr'));
            $data['sjrdh'] = trim($this->request->param('sjrdh'));
            $data['bz'] = trim($this->request->param('bz'));
            $valid = new Valid([
                'title'=>'require',
                'wlsqc'=>'require',
                'wlsdz'=>'require',
                'lxr'=>'require',
                'dh'=>'require',
                'yjdz'=>'require',
                'sjr'=>'require',
                'sjrdh'=>'require',
            ]);

            if(!$valid->check($data)){
                echo json_encode(['success'=>false,'msg'=>$valid->getError()]);die;
            }
            $data['puser'] = session('employee_id');
            $data['ptime'] = date('Y-m-d H:i:s');
            $bool = db('cg_wlgs')->where('id',$id)->update($data);
            if(false!==$bool){
                $this->updateAttach();
                echo json_encode(['success'=>true,'msg'=>'修改成功']);
            }else{
                echo json_encode(['success'=>false,'msg'=>'修改失败']);
            }
            die;
        }
        $data = db('cg_wlgs')->where('id',$id)->find();
        $this->assign('data',$data);

        //查询附件
        $attach = db('cg_wlgs')->where('id',$id)->value('attach');
        if($attach){
            $attach = unserialize($attach);
            foreach($attach as $k=>$v){
                $temp = db('annex')->where(array('id'=>$v))->find();
                $temp['path'] = '/uploads/'.$temp['path'];
                $attach[$k] = $temp;
            }
        }
        $this->assign('attach',$attach);

        return $this->fetch('form');
    }

    /**
     * 修改附件--新增、修改申请时
     * @return mixed
     */
    private function updateAttach(){
        $attach = $this->request->post('attach');
        $id = $this->request->param('id');
        $attach = trim($attach,',');
        $attachids = $attach;

        //先把旧的附件查出来
        $aids = db('cg_wlgs')->where('id',$id)->value('attach');
        if($aids){
            $aids = unserialize($aids);
        }
        if(!is_array($aids)){
            $aids = [];
        }

        //删除附件
        $remove = $this->request->post('remove');

        if($remove){
            $aids = array_diff($aids,explode(',',$remove));
            db('annex')->where('id','in',$remove)->update(['isdel'=>1]);
        }

        //新增附件
        if($attach) {
            $attachids = $attach;
            //新旧合并
            $attach = serialize(array_merge(explode(',', $attach), $aids));
        }else { //非新增
            $attach = serialize($aids);
        }

        $data = [];
        $data['attach'] = $attach;
        db('cg_wlgs')->where(array('id'=>$id))->update($data);
        //新增附件\更新附件表
        if(isset($attachids)){
            db('annex')->where(array('id'=>array('in',$attachids)))->update(array('target_id'=>$id,'target_table'=>'cg_wlgs'));
        }

    }

    /**
     * 删除物流商
     */
    public function delete()
    {
        $id = $this->request->param('id');
        $bool = db('cg_wlgs')->where('id',$id)->delete();
        if(false!==$bool){
            echo json_encode(['success'=>true,'msg'=>'删除成功']);
        }else{
            echo json_encode(['success'=>false,'msg'=>'删除失败']);
        }
    }

    /**
     * 导入物流商
     */
    public function lend()
    {
        return $this->fetch();
    }

    //导入
    public function upload()
    {
        // 获取表单上传文件
        $file = request()->file('file');

        // 移动到框架应用根目录/public/uploads/bx_guide 目录下
        $info = $file->validate(['ext' => 'xls,xlsx', 'size' => 105678])->move(ROOT_PATH . 'public' . DS . 'uploads' . DS . 'bx_guide');
        if ($info) {
            // 成功上传后 获取上传信息
            $data = self::testExcel($info->getSaveName());
            echo "<script>top.sweetAlert('" . $data['msg'] . "','','" . $data['success'] . "');parent.location.reload();</script>";
        } else {
            // 上传失败获取错误信息
            echo "<script>top.sweetAlert('" . $file->getError() . "','','warning');</script>";
        }
    }

    /**
     * excel导入示例
     */
    public function testExcel($file)
    {
        Vendor("PHPExcel");
        Vendor("PHPExcel.IOFactory");
        Vendor("PHPExcel.Reader.Excel5");

        $url = ROOT_PATH . 'public' . DS . 'uploads' . DS . 'bx_guide' . DS . $file;
        $objReader = PHPExcel_IOFactory::createReaderForFile($url);
        $objPHPExcel = $objReader->load($url);
        $sheet=$objPHPExcel->setActiveSheetIndex(0);
        $highestRow = $sheet->getHighestRow(); // 取得总行数
        $highestColumm = $sheet->getHighestColumn(); // 取得总列数

        $sn = $this->produceSN();
        $ins = [];
        for ($row = 4; $row <= $highestRow; $row++) {//行数是以第4行开始
            if (!trim($sheet->getCell('A' . $row)->getValue())) {
                break;
            }
            $value = trim($sheet->getCell('A' . $row)->getValue());
            $is_cunzai = db('cg_wlgs')->where('sn',$value)->count();
            if($is_cunzai){
                // 上传失败获取错误信息
                echo "<script>top.sweetAlert('物流公司编号：".$value."已经存在','','warning');</script>";die;
            }
            $t = [
                'sn'=>$value?$value:$sn,
                'title'=>trim($sheet->getCell('B' . $row)->getValue()),
                'wlsqc'=>trim($sheet->getCell('C' . $row)->getValue()),
                'wlsdz'=>trim($sheet->getCell('D' . $row)->getValue()),
                'lxr'=>trim($sheet->getCell('E' . $row)->getValue()),
                'dh'=>trim($sheet->getCell('F' . $row)->getValue()),
                'gscz'=>trim($sheet->getCell('G' . $row)->getValue()),
                'yjdz'=>trim($sheet->getCell('H' . $row)->getValue()),
                'sjr'=>trim($sheet->getCell('I' . $row)->getValue()),
                'sjrdh'=>trim($sheet->getCell('J' . $row)->getValue()),
                'bz'=>trim($sheet->getCell('K' . $row)->getValue()),
                'cuser'=>session('employee_id'),
                'ctime'=>date('Y-m-d H:i:s'),
                'puser'=>session('employee_id'),
                'ptime'=>date('Y-m-d H:i:s'),
            ];
            $ins[] = $t;
            $sn++;

        }
        $bool = db('cg_wlgs')->insertAll($ins);
        if(false!==$bool){
            echo "<script>top.sweetAlert('导入成功','','success');location.reload();</script>";
        }else{
            echo "<script>top.sweetAlert('导入失败','','warning');location.reload();</script>";
        }
        die;
        $reader = new ExcelReader($url);
        $titlesMap = [
            'cityType' => '物流商编号',
            'salaryLevel' => '物流商简称',
            'basePay' => '物流',
        ];
        // 是否提供titlesMap 返回的数据结构不一致
        $data = $reader->exec($titlesMap);
        dump($data);die;
        if ($data) {
            unset($data[0]);
            $is_empty = true;
            foreach ($data as $key => $val) {
                foreach ($val as $k => $v) {
                    if (!empty($v)) {
                        $is_empty = false;
                        break;
                    }
                    if (!$is_empty) unset($data[$key]);

                }
            }
            if ($data) {
                $cityType = ['一类城市', '二类城市', '三类城市'];
                $cityType1 = implode(',', $cityType);
                $validate = new Validate([
                    ['cityType', 'require|in:' . $cityType1, '城市类别必须|城市类别不存在'],
                    ['salaryLevel', 'require', '导购薪资级别名称必须'],
                    ['basePay', 'require', '基本工资必须'],
                ]);
                $off = 1;
                foreach ($data as $key => $val) {
                    if ($validate->check($val)) {
                        $off = 1;
                    } else {
                        $off = 0;
                        break;
                    }
                }

                if ($off === 1) {
                    foreach ($data as $key => $val) {
                        if ($val['cityType'] == '一类城市') {
                            $tmp[] = $val['salaryLevel'];
                            $tmp1 = array_count_values($tmp);
                        } else if ($val['cityType'] == '二类城市') {
                            $tmp2[] = $val['salaryLevel'];
                            $tmp21 = array_count_values($tmp2);
                        } else {
                            $tmp3[] = $val['salaryLevel'];
                            $tmp31 = array_count_values($tmp3);
                        }
                    }
                    if (self::count($tmp1) == 3) {
                        if (self::count($tmp21) == 3) {
                            if (self::count($tmp31) == 3) {
                                if (Db::table('bx_guide_salaryLevel')->insertAll($data)) {
                                    return [
                                        "success" => 'success',
                                        'msg' => '上传文件成功'
                                    ];
                                } else {
                                    return [
                                        "success" => 'warning',
                                        'msg' => '网络错误！请重试'
                                    ];
                                }
                            } else {
                                return [
                                    'success' => false,
                                    'msg' => '三类城市下导购薪资级别名称重复！'
                                ];
                            }
                        } else {
                            return [
                                'success' => false,
                                'msg' => '二类城市下导购薪资级别名称重复！'
                            ];
                        }
                    } else {
                        return [
                            'success' => false,
                            'msg' => '一类城市下导购薪资级别名称重复！'
                        ];
                    }
                } else {
                    return [
                        "success" => 'warning',
                        "msg" => $validate->getError()
                    ];
                }
            } else {
                return [
                    "success" => 'warning',
                    'msg' => '请填充数据'
                ];
            }
        } else {
            return [
                "success" => 'warning',
                'msg' => '网络错误！请重试'
            ];
        }
    }

    /**
     * 详情
     */
    public function view()
    {
        $id = $this->request->param('id');
        $data = db('cg_wlgs')->where('id',$id)->find();
        $this->assign('data',$data);
        return $this->fetch();
    }
    /**
     * 导出
     */
    public function excel()
    {
        $sn = $this->request->param('sn');
        $title = $this->request->param('title');
        $this->assign([
            'title'=>$title,
            'sn'=>$sn,
        ]);
        $where = [];
        if($sn){
            $where['sn'] = ['like','%'.trim($sn).'%'];
        }
        if($title){
            $where['title'] = ['like','%'.trim($title).'%'];
        }
        $data = db('cg_wlgs')->where($where)->select();

        Vendor("PHPExcel");
        Vendor("PHPExcel.Writer.Excel5");
        $objPHPExcel= new PHPExcel();
        $objProps=$objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $as =  $objPHPExcel->getActiveSheet();
        $as->setTitle("物料商列表");
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的
                    'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框
                    'color' => array('argb' => ''),
                ),
            ),
        );
        $as->mergeCells('A1:I1');
        $as->setCellValue('A1','物料商列表');
        //加粗
        $as->getStyle('A1')->getFont()->setBold(true);
        //字体
        $as->getStyle('A1')->getFont()->setSize(14);
        $as->mergeCells('A2:I2');
        $username = db('employee')->where('id',session('employee_id'))->value('em_name');
        $as->setCellValue('A2','注：本表单由软件导出，导出时间：'.date('Y-m-d H:i').'导出人：'.$username);
        $as->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $as->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        //标题
        $as->getStyle('A3:I3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $as->getStyle('A3:I3')->getFill()->getStartColor()->setARGB('0099CCFF');
        $as->setCellValue('A3','No.');
        $as->setCellValue('B3','物流商编号');
        $as->setCellValue('C3','物流商简称');
        $as->setCellValue('D3','联系人姓名');
        $as->setCellValue('E3','联系人电话');
        $as->setCellValue('F3','添加人');
        $as->setCellValue('G3','添加时间');
        $as->setCellValue('H3','修改人');
        $as->setCellValue('I3','修改时间');

        foreach($data as $k=>$v){
            $n = 4+$k;
            $as->setCellValue('A'.$n,$k+1);
            $as->setCellValue('B'.$n,''.$v['sn']);
            $as->setCellValue('C'.$n,$v['title']);
            $as->setCellValue('D'.$n,$v['lxr']);
            $as->setCellValue('E'.$n,$v['dh']);
            $as->setCellValue('F'.$n,$v['cuser']);
            $as->setCellValue('G'.$n,$v['ctime']);
            $as->setCellValue('H'.$n,$v['puser']);
            $as->setCellValue('I'.$n,$v['ptime']);
        }

        $n = $as->getHighestRow();
        $as->getStyle('A1:I'.$n)->applyFromArray($styleArray);
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=物流商列表.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }
}